package cn.xieyang.gen.util;

import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

import java.util.*;

public class Excel2SqlUtils {
    public static void main(String[] args) {
        LinkedHashMap<String, String> map = new LinkedHashMap<>();
        map.put("job_id", "varchar");
        map.put("job_platform", "varchar");
        map.put("job_url", "varchar");
        map.put("job_name", "varchar");
        map.put("job_company_name", "varchar");
        map.put("job_location_name", "varchar");
        map.put("job_address", "varchar");
        map.put("job_longitude", "decimal");
        map.put("job_latitude", "decimal");
        map.put("job_description", "varchar");
        map.put("job_degree_name", "varchar");
        map.put("job_year", "tinyint");
        map.put("job_salary_min", "decimal");
        map.put("job_salary_max", "decimal");
        map.put("job_salary_total_month", "tinyint");
        map.put("job_first_publish_datetime", "datetime");
        map.put("boss_name", "varchar");
        map.put("boss_company_name", "varchar");
        map.put("boss_position", "varchar");
        map.put("create_datetime", "varchar");
        map.put("update_datetime", "varchar");
        String tableName = "job_tbl";
        String path = "D:\\download\\20250323161204 - 副本.xlsx";
        Integer startRow = 2;
        String sql = getSql(tableName, map, path, "Data", startRow);
        System.out.println(sql);
    }

    public static String getSql(String tableName, LinkedHashMap<String, String> columnNameMap, String path, String sheetName, Integer startRow) {
        StringBuilder sb = new StringBuilder();
        LinkedList<String> valueTypeList = new LinkedList<>();
        for (String value : columnNameMap.values()) {
            valueTypeList.add(value);
        }
        ExcelReader reader = ExcelUtil.getReader(path, sheetName);
        for (int i = startRow - 1; i <= reader.getRowCount(); i++) {
            sb.append("INSERT INTO `" + tableName + "` (");
            int index = 0;
            for (String s : columnNameMap.keySet()) {
                sb.append("`" + s + "`,");
                index++;
                if (index == columnNameMap.size()) {
                    sb.deleteCharAt(sb.length() - 1);
                }
            }
            sb.append(" ) VALUES  ( ");
            List<Object> row = reader.readRow(i);
            for (int j = 0; j < row.size(); j++) {
                if (strTypeSet.contains(valueTypeList.get(j).toLowerCase())) {
                    sb.append("'" + (Objects.isNull(row.get(j)) ? "" : row.get(j)) + "',");
                } else if (notStrTypeSet.contains(valueTypeList.get(j).toLowerCase())) {
                    sb.append(getValue(row.get(j)) + ",");
                } else if (dateTimeTypeSet.contains(valueTypeList.get(j).toLowerCase())) {
                    if (row.get(j) == null) sb.append("NULL,");
                    else sb.append("'" + row.get(j) + "',");
                } else {
                    sb.append("'" + row.get(j) + "',");
                }
                if (j == row.size() - 1) {
                    sb.deleteCharAt(sb.length() - 1);
                }
            }
            sb.append(" );");
            sb.append("\n");
        }
        return sb.toString();
    }

    private static Object getValue(Object value) {
        if (value == null) return "NULL";
        if (value instanceof String && StrUtil.isBlank((String) value)) return "NULL";
        return value;
    }


    public static Set<String> strTypeSet = new HashSet<>();
    public static Set<String> notStrTypeSet = new HashSet<>();
    public static Set<String> dateTimeTypeSet = new HashSet<>();

    static {
        // 字符类型
        strTypeSet.add("char");
        strTypeSet.add("text");
        strTypeSet.add("varchar");
        // 非字符类型
        notStrTypeSet.add("tinyint");
        notStrTypeSet.add("decimal");
        // 时间类型
        dateTimeTypeSet.add("datetime");
    }

}
